package maitaole.dao.roleDao;

import maitaole.bean.Role;
import maitaole.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author 李杰
 * @Description 类的功能描述
 * @Date 2023/5/16
 */
public class RoleDao {
    public List<Role> selectRoles(){
        List<Role> roleList = new ArrayList<>();
        try {
            String sql ="select role_id roleId,role_name roleName,role_desc roleDesc from tb_roles";
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDs());
            roleList = queryRunner.query(sql, new BeanListHandler<Role>(Role.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return roleList;
    }

    /**
     * 分页查询
     * @param pageNum
     * @param pageSize
     * @return
     */
    public List<Role> selectRolesPage(Integer pageNum,Integer pageSize){
        Integer pageStart = (pageNum-1)*pageSize;
        List<Role> roleList = new ArrayList<>();
        try {
            String sql ="select role_id roleId,role_name roleName,role_desc roleDesc from tb_roles limit ?,?";
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDs());
            roleList = queryRunner.query(sql, new BeanListHandler<Role>(Role.class),pageStart,pageSize);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return roleList;
    }

    /**
     * 插入角色
     * @param role
     * @return
     */
    public int insertRole(Role role){
        int i = 0;
        try {
            String sql = "insert into tb_roles (role_name,role_desc) values(?,?)";
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDs());
            BigInteger object = queryRunner.insert(sql, new ScalarHandler<>(), role.getRoleName(), role.getRoleDesc());
            i = object.intValue();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    public int insertRole2(Role role,Connection conn){
        int i = 0;
        String sql = "insert into tb_roles (role_name,role_desc) values(?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,role.getRoleName());
            ps.setString(2,role.getRoleDesc());
            ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()){
               i=rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return i;
    }


    /**
     * 保存角色和权限信息
     * @param roleId
     * @param menuId
     */
    public int insertRoleAndMenu(int roleId,int menuId){
        int i = 0;
        try {
            String sql = "insert into tb_role_menu(role_id,menu_id) values(?,?)";
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDs());
             i = queryRunner.update(sql,roleId, menuId);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;

    }

    public int insertRoleAndMenu2(int roleId,int menuId,Connection conn){
        int i = 0;
        String sql = "insert into tb_role_menu(role_id,menu_id) values(?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,roleId);
            ps.setInt(2,menuId);
            i = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return i;

    }

    public static void main(String[] args) {
        Role role = new Role();

        RoleDao roleDao = new RoleDao();

    }

}



